package com.wondertek.service.mapper.business;


import com.wondertek.service.dto.business.res.*;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author wangyu
 */
public interface WebHomePageMapper {

    @Select("select (select count(1) from consumer) customerNum,(select count(1) from site where is_delete = 0) siteNum, (select count(1) from device_error_record where type in(1,2,3,4)) alarmsNum,(select count(1) from device where is_delete = 0)+(select count(1) from accessory where is_delete = 0) deviceAccessoryNum from dual")
    WhpTopRes findTop();

    @Select("select site_name,address,sum(device_sum) deviceAccessorySum,sum(offline_sum) offlineSum,sum(roomSum) roomSum,sum(alarmSum)alarmSum from ( select count(d.id) device_sum,s.site_name,s.address,count(if(d.status = 0,1,null)) offline_sum, sum((select count(1) from room r where r.is_delete = 0 and r.device_id = d.id)) roomSum,sum((select count(1) from device_error_record der where der.device_id = d.sn_code and type in(1,2,3,4))) alarmSum from device d left join site s on d.site_id = s.id  where d.is_delete = 0 and d.site_id = #{siteId} union all select count(a.id),'','',count(if(a.status = 0,1,null)),0,0 from accessory a where a.is_delete = 0 and room_id in ( select id from room r where r.is_delete = 0 and exists (select 1 from device d where d.is_delete = 0 and d.id = r.device_id and d.site_id = #{siteId}) ) ) res")
    WhpMapSiteRes findMapSiteBySiteId(@Param("siteId") String siteId);

    // 需程序算比例 移除top5以外的算出其他
    @Select("select * from ( select case (select category from device_type_detail where id = device_type_id)  when 1 then 'IPC' when 2 then 'alarm hub' when 3 then 'other' when 4 then 'accessory' else '-' end as device_type ,count(1) sum from device d where d.is_delete = 0 group by device_type_id union all select case accessory_type when 1 then 'Smoke detector' when 2 then 'Wireless keyfob' when 3 then 'External Siren' when 4 then 'Door Detector' when 5 then 'Door Detector Plus' when 6 then 'Pir Detertor' else '-' end as accessory_type ,count(1) from accessory a where a.is_delete = 0 group by accessory_type ) res order by sum desc")
    List<WhpRightRes> findRight();

    @Select("select (select count(1)  from site where create_time >= date_sub(curdate(), interval 30 day)) addSiteSum, (select count(1)  from device_error_record where error_time >= date_sub(curdate(), interval 30 day) and type in(1,2,3,4)) addAlarmSum, (select  (select count(1) from device where create_time >= date_sub(curdate(), interval 30 day))+ (select count(1) from accessory where   create_time >= date_sub(curdate(), interval 30 day))  from dual ) addDeviceAccessorySum, (select count(1)  from rental_device where  create_time >= date_sub(curdate(), interval 30 day)) addRentalSum from dual")
    WhpBottomLeftRes findBottomLeft();

    @Select("select date(error_time) date ,count(1) alarmSum from device_error_record where error_time >= date_sub(curdate(), interval 30 day) and type in(1,2,3,4)  group by date(error_time) order by date(error_time)")
    List<WhpBottomCentreRes> findBottomCentre();

    @Select("select count(1) sum,count(if(status = 0,1,null)) offLineSum from ( select id,status from device where is_delete = 0 union all select id,status from accessory where is_delete =0 ) res")
    WhpBottomRightRes findBottomRight();

    // 原型待确认 :  bottomRight
}
